package business.manage.dictionary;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import common.database.anno.MySqlTransactional;
import common.database.bean.Page;
import common.database.interfaces.BaseDAO;
@Service
public class DictionaryService {
	@Autowired
	private BaseDAO baseDao;
	
	private static String INSERT_SQL = "INSERT INTO sys_dictionary(   "+
			" id,  code,  name,  remarks,  createBy,  createdate,  updateBy,  updatedate,pid)"+
			"VALUES( :id,  :code,  :name,  :remarks,  :createBy,  :createDate,  :updateBy,  :updateDate,:pid)";
	
	private static String SELECT_SQL = "SELECT                      "+
										"  t.id AS Id,               "+
										"  pid,                      "+
										"  CODE AS CODE,             "+
										"  NAME AS NAME,             "+
										"  remarks AS remarks,       "+
										"  u.`person_name` createBy,              "+
										"  t.createdate createDate,  "+
										"  t.updateBy updateBy,      "+
										"  t.updatedate updateDate   "+
										"FROM                        "+
										"  sys_dictionary t left join         "+
										"  sys_user u                "+
										"on t.`createBy` = u.`Id` where 1=1 ";
	
	private static String DELETE_SQL = "delete from sys_dictionary where id = ?";
	
	private static String UPDATE_SQL =  "update sys_dictionary      "+
										"set pid = :pid,         "+
										"  code  = :code,      "+
										"  name  = :name,      "+
										"  remarks = :remarks, "+
										"  createBy = :createBy,    "+
										"  createdate = :createDate,"+
										"  updateBy = :updateBy,    "+
										"  updatedate  = :updateDate "+
										" where id = :id";
	
	private static String SELECT_PRODUCT_SUM_SQL =	"SELECT                    "+
													"  s.`name`,               "+
													"  s.`code`,               "+
													"  p.`product_type`,       "+
													"  COUNT(*) brand_product_count               "+
													"FROM                      "+
													"  ph_product p,           "+
													"  sys_dictionary s        "+
													"WHERE p.brand = s.`code`  "+
													"  AND p.`is_show` = '1'   "+
													"GROUP BY s.name,s.`code`, "+
													"  p.`product_type` ORDER BY s.`remarks` ";
	
	private static String SELECT_PRODUCT_ADVANTAGE_SUM_SQL =	"SELECT                    "+
													"  s.`name`,               "+
													"  s.`code`,               "+
													"  p.`product_type`,       "+
													"  COUNT(*) brand_product_count               "+
													"FROM                      "+
													"  ph_product p,           "+
													"  sys_dictionary s        "+
													"WHERE p.brand = s.`code`  "+
													"  AND p.`is_show` = '1'   "+
													"  AND p.`is_advantage` = '1'   "+
													"GROUP BY s.name,s.`code`, "+
													"  p.`product_type` ORDER BY s.`remarks` ";
	
	
	public Dictionary getDictionaryById(String id)throws Exception{
		StringBuffer sb = new StringBuffer(SELECT_SQL);
		sb.append(" and t.id=?");
		return (Dictionary)baseDao.getJavaBean(sb.toString(), Dictionary.class, id);
	}
	public Page listDictionaryByType(String typeId,Page page)throws Exception{
		page = baseDao.getListByPage(SELECT_SQL, page, Dictionary.class, typeId);
		return page;
	}
	
	public Page listDictionary(DictArgument da,Page page)throws Exception{
		StringBuffer sb = new StringBuffer(SELECT_SQL);
		List<String> list = new ArrayList<String>();
		if(da.getPid()!=null&&!da.getPid().equals("")){
			sb.append(" and pid=?");
			list.add(da.getPid());
		}
		
		sb.append(" order by remarks");
		page = baseDao.getListByPage( sb.toString(), page, Dictionary.class,list.toArray());
		return page;
	}
	
	public List<Dictionary> listAllDictionary()throws Exception{
		return  (List<Dictionary>)baseDao.getList(SELECT_SQL, Dictionary.class);
	}
	
	public List<Dictionary> listAllPid()throws Exception{
		String sql = SELECT_SQL+" and pid='0' order by createDate desc";
		List<Dictionary> result = baseDao.getList(sql, Dictionary.class);
		return result;
	}
	
	public int insert(Dictionary dict)throws Exception{
		int result = baseDao.update(INSERT_SQL, dict);
		return result;
	}
	
	public int deleteById(String id)throws Exception{
		int result = baseDao.update(DELETE_SQL,id);
		return result;
	}
	
	@MySqlTransactional
	public boolean deleteByArrayId(String[] ids) throws Exception{
		int sum = 0;
		for(int i=0;i<ids.length;i++){
			sum =+ baseDao.commonUpdate(DELETE_SQL, ids[i]);
		}
		if(ids.length!= sum){
			throw new Exception("DELETE FAULURE!");
		}
		return true;
	}
	
	public int update(Dictionary dict)throws Exception{
		int result = baseDao.update(UPDATE_SQL, dict);
		return result;
	}
	
}
